create or replace
procedure dsh_archive (current_pid in number)
as
    -- current affected item of this pid
    cursor ais is select item_number,sites from source_affected_items where pid = current_pid and sites is not null;
    -- all previous pid that need to be updated
    cursor update_ai_pids(current_ai_number varchar2, current_ai_site varchar2) is select pid from source_affected_items where item_number = current_ai_number and sites = current_ai_site and pid < current_pid and sync_state != 1;
    -- all bom of previous affected_item
    cursor ais_bom(current_ai_number varchar2, current_ai_site varchar2, ai_pid number) is select timestamp,child_item_number
        from source_bom b
        where pid = ai_pid and sync_state = 0
        start with item_number = current_ai_number and site_name = current_ai_site
        connect by item_number = prior child_item_number and site_name = current_ai_site;
    --ai item_number
    current_ai_number source_affected_items.item_number%type;
    --ai site
    current_ai_site source_affected_items.sites%type;
    --ai_bom_timestamp
    ai_bom_timestamp source_bom.timestamp%type;
    ai_bom_child_no varchar(200);
    --need to be updated
    ai_pid number;
    ai_count number;
    ai_bom_count number;
begin
    open ais;
    loop
        fetch ais into current_ai_number, current_ai_site;
    exit when ais%notfound;
        ai_count := 0;
        select count(*) into ai_count from source_affected_items where pid < current_pid and item_number = current_ai_number;
        if ai_count > 0 then
            -- bom
            open update_ai_pids(current_ai_number, current_ai_site);
            loop
                fetch update_ai_pids into ai_pid;
            exit when update_ai_pids%notfound;
                ai_bom_count :=0;
                -- bom count
                select count(1) into ai_bom_count from source_bom where pid = ai_pid and item_number = current_ai_number and site_name = current_ai_site;
                if ai_bom_count > 0 then
                    open ais_bom(current_ai_number, current_ai_site, ai_pid);
                    loop
                        fetch ais_bom into ai_bom_timestamp, ai_bom_child_no;
                    exit when ais_bom%notfound;
                        update source_bom a set sync_state = 1 where a.timestamp = ai_bom_timestamp;
                        update source_item set sync_state = 1 where item_number = ai_bom_child_no and pid = ai_pid;
                        update source_item_site set sync_state = 1 where item_number = ai_bom_child_no and pid = ai_pid;
                    end loop;
                    close ais_bom;
                end if;
            end loop;
            close update_ai_pids;
            -- item
            update source_item set sync_state = 1 where pid < current_pid and sync_state = 0 and item_number = current_ai_number;
            -- site
            update source_item_site set sync_state = 1 where pid < current_pid and sync_state = 0 and item_number = current_ai_number;
            -- affected_item
            update source_affected_items set sync_state = 1 where pid < current_pid and sync_state = 0 and item_number = current_ai_number and sites = current_ai_site;
        end if;
    end loop;
    close ais;
end;